//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Copyright 2007 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   CATALOG_DTL_TEMPLATE
// AUTHOR           :   Craig Chun
// DATE CREATE      :   2007/07/20
// DESCRIPTION      :   a COMMON DAO file for the function, CATALOG_DTL_TEMPLATE
//                  :
//                  :
//                  :
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2007/07/20   Craig Chun      1.0                 source is generated.
//
//
//
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

package com.erp.app.setup.itemDetailTemplate.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.common.util.ProjectCommonUtil;
import com.erp.app.setup.itemDetailTemplate.bizLogic.CatalogDtlTemplateCommonUtil;
import com.erp.app.setup.itemDetailTemplate.criteria.CatalogDtlTemplateSrchCriteria;
import com.erp.app.setup.itemDetailTemplate.vo.CatalogDtlTemplateVo;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;

/**
* <p>A <strong>CommonDao</strong> provides database access functions
*  for delegate classes.
*
* @author          Craig Chun
* @version 1.0     2007/07/20
* @since           FrameWork 1.1
*
*/
public class CatalogDtlTemplateCommonDao {

	private CatalogDtlTemplateCommonUtil util = new CatalogDtlTemplateCommonUtil();

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Craig Chun
	* @since      Framework 1.1
	*/
	public static String getHdrSqlQuery(SimpleBaseSrchCriteria obj) {

		CatalogDtlTemplateSrchCriteria criteria = (CatalogDtlTemplateSrchCriteria) obj;
		String sql = "";
		String whereClause = hdrToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY COMPANY_CODE, NODE_ID, DTL_ID";

		if(!criteria.isSearchResult()){
			sql = "SELECT COMPANY_CODE, NODE_ID, DTL_ID, SEQ, STATUS, "
			+ "BROWSER_COLUMN "
			+ "FROM T_CATALOG_DTL_TEMPLATE "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		} else {
			sql = " SELECT   a.company_code,a.node_id, a.DTL_ID, a.SEQ, "+
			" a.STATUS, "+
			" a.browser_column, d.ref_desc, "+
			" d.ref_desc DTL_TYPE, c.DTL_NAME "+
			" FROM T_CATALOG_DTL_TEMPLATE a, "+
			" t_catalog b, "+
			" T_catalog_dtl c, "+
			" t_code_dtl d "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		}

		return sql;
	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Craig Chun
	* @since      Framework 1.1
	*/
	public static SimpleBaseVo getHdrSearchResultSetValues(ResultSet rs) throws SQLException {

		CatalogDtlTemplateVo vo=new CatalogDtlTemplateVo();
		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setNodeId(CustomBigInteger.customize(rs.getBigDecimal("node_id")));
		vo.setDtlId(CustomBigInteger.customize(rs.getBigDecimal("DTL_ID")));
		vo.setItemDetailType(rs.getString("DTL_TYPE"));
		vo.setItemDetailName(rs.getString("DTL_NAME"));
		vo.setHdrSeq(CustomBigInteger.customize(rs.getBigDecimal("SEQ")));
		vo.setHdrStatus(rs.getString("STATUS"));
		vo.setHdrBrowserColumn(CustomBigInteger.customize(rs.getBigDecimal("browser_column")));

		return vo;

	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Craig Chun
	* @since      Framework 1.1
	*/
	public static String hdrToWhereClause(CatalogDtlTemplateSrchCriteria crit) {

		CatalogDtlTemplateSrchCriteria criteria = (CatalogDtlTemplateSrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		if (criteria.getSrchCompanyCode() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append(" a.COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
		}

		if (criteria.getSrchCompanyCode() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append("  a.node_id = b.node_id "+
					" AND b.company_code = c.company_code "+
					" AND a.DTL_ID = c.DTL_ID "+
					" AND c.company_code = d.company_code(+) "+
					" AND c.DTL_TYPE = d.ref_code(+) "+
					" AND (d.ref_master_code IS NULL OR d.ref_master_code = 'CATALOG_DTL_TYPE' "+
					" )");
		}

		ProjectCommonUtil.addHdrEquals(whereClause,  "a.node_id", criteria.getSrchNodeIdFr());
		ProjectCommonUtil.addHdrEquals(whereClause,  "c.DTL_TYPE", criteria.getSrchitemDetailType());
		ProjectCommonUtil.addHdrEquals(whereClause,  "a.STATUS", criteria.getSrchHdrStatus());
		ProjectCommonUtil.addHdrLike(whereClause,    "c.DTL_NAME", criteria.getSrchitemDetailName());



		return whereClause.toString();
	}

}
